package dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import dao.UserDao;
import domain.Product;
import domain.User;
import util.DataSourceUtils;

public class UserDaoImpl implements UserDao {

	@Override
	public User login(User user) throws SQLException {
		String sql = "select user.* from user where username=? and PASSWORD=?;";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

		return runner.query(sql, new ResultSetHandler<User> () {
			@Override
			public User handle(ResultSet rs) throws SQLException {
				while (rs.next()) {
					User user = new User();
					user.setId(Integer.parseInt(rs.getString("user.id")));
					user.setUsername(rs.getString("user.username"));
					user.setPassword(rs.getString("user.PASSWORD"));
					user.setGender(rs.getString("user.gender"));
					user.setEmail(rs.getString("user.email"));
					user.setTelephone(rs.getString("user.telephone"));
					user.setIntroduce(rs.getString("introduce"));
					user.setRole(rs.getString("role"));
					user.setRegistTime(rs.getDate("registTime"));
					return user;
				}
				return null;
			}
		}, user.getUsername(), user.getPassword());
	}

	@Override
	public boolean register(User user) throws SQLException {
		String sql = "insert into user(username,PASSWORD,gender,email,telephone,introduce,role) values(?,?,?,?,?,?,?)";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		try {
			runner.update(sql, user.getUsername(), user.getPassword(), user.getGender(), user.getEmail(), user.getTelephone(), user.getIntroduce(), user.getRole());
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public User signin(User user) throws SQLException {
		String sql = "select user.* from user where username=? and PASSWORD=? and role='user';";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

		return runner.query(sql, new ResultSetHandler<User> () {
			@Override
			public User handle(ResultSet rs) throws SQLException {
				while (rs.next()) {
					User user = new User();
					user.setId(Integer.parseInt(rs.getString("user.id")));
					user.setUsername(rs.getString("user.username"));
					user.setPassword(rs.getString("user.PASSWORD"));
					user.setGender(rs.getString("user.gender"));
					user.setEmail(rs.getString("user.email"));
					user.setTelephone(rs.getString("user.telephone"));
					user.setIntroduce(rs.getString("introduce"));
					user.setRole(rs.getString("role"));
					user.setRegistTime(rs.getDate("registTime"));
					return user;
				}
				return null;
			}
		}, user.getUsername(), user.getPassword());
	}

	@Override
	public User findUserById(int id) throws SQLException {
		String sql = "select user.* from user where id=? limit 0,1;";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

		return runner.query(sql, new ResultSetHandler<User> () {
			@Override
			public User handle(ResultSet rs) throws SQLException {
				while (rs.next()) {
					User user = new User();
					user.setId(Integer.parseInt(rs.getString("user.id")));
					user.setUsername(rs.getString("user.username"));
					user.setGender(rs.getString("user.gender"));
					user.setEmail(rs.getString("user.email"));
					user.setTelephone(rs.getString("user.telephone"));
					user.setIntroduce(rs.getString("introduce"));
					user.setRole(rs.getString("role"));
					user.setRegistTime(rs.getDate("registTime"));
					return user;
				}
				return null;
			}
		}, id);
	}

	@Override
	public boolean updateUser(User user) throws SQLException {
		String sql = "update user set username=?,gender=?,email=?,telephone=?,introduce=? where id=?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		try {
			runner.update(sql, user.getUsername(), user.getGender(), user.getEmail(), user.getTelephone(), user.getIntroduce(), user.getId());
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public List<User> findUsers(String id, String username, int page, int limit) throws SQLException {
		String sql = "select * from user where username like ? and id like ? order by registTime desc limit ?,?";
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());

		return runner.query(sql, new ResultSetHandler<List<User>> () {
			@Override
			public List<User> handle(ResultSet rs) throws SQLException {
				List<User> res = new ArrayList<User>();
				
				while (rs.next()) {
					User user = new User();
					user.setId(Integer.parseInt(rs.getString("user.id")));
					user.setUsername(rs.getString("user.username"));
					user.setPassword(rs.getString("user.PASSWORD"));
					user.setGender(rs.getString("user.gender"));
					user.setEmail(rs.getString("user.email"));
					user.setTelephone(rs.getString("user.telephone"));
					user.setIntroduce(rs.getString("introduce"));
					user.setRole(rs.getString("role"));
					user.setRegistTime(rs.getDate("registTime"));
					
					res.add(user);
				}
				
				return res;
			}
		}, "%" + username + "%", "%" + id + "%", (page - 1) * limit, limit);
	}

}
